package com.expdatacloud.utils;

/**
 * 这里需要用到到的包，就怕你们导包不知道导哪个
 */
import com.expdatacloud.entity.YtWorkOrderDetail;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public  class GetListUtil {

    public static void main(String[] args) {
        System.out.println(GetListUtil.getAllListUtil().size());
    }

    public static List<YtWorkOrderDetail> getAllListUtil() {
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        List<YtWorkOrderDetail> people=new ArrayList<>();
        List<Map<String,String>> list = null;
        String cellData = null;
        //需要修改的地方1.filePath:你需要导入的文件，xls与xlsx文件都可以
        String filePath = "C:\\Users\\17229\\Desktop\\123.xlsx";
        //需要修改的地方2.columns[]:这里是你的Excel表头，一定要写对啊，最好是字母
//        String columns[] = {"WOD001","STORERKEY","WOD003","WOD004","WOD005","WOD009","WOD010",
//                "WOD011","WOD012","WOD013","WOD014","WOD015","WOD016","WOD017","WOD018","WOD019","WOD020","WOD021","WOD023",
//                "WOD024","WOD025","WOD026","WOD027","WOD029","MINQTY"};
        String columns[] = {"RECEIPTKEY","RECEIPTLINENUMBER","QTY","TOLOC","FROMLOC","LOT01"};
        wb = readExcel(filePath);
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>(10000);
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
                    for (int j=0;j<colnum;j++){
                        cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                }else{
                    break;
                }
                list.add(map);
            }
        }
        //  现在获取到所有的数据集合了
        //需要修改的地方3. 现在获取到的map集合，将其存入list中去
        for (Map<String,String> map : list) {
            YtWorkOrderDetail people1=new YtWorkOrderDetail();

            people1.setReceiptkey(map.get("RECEIPTKEY"));
            people1.setLinumber(map.get("RECEIPTLINENUMBER"));
            people1.setQty("".equals(map.get("QTY")) ? 0 : Double.parseDouble(map.get("QTY")));
            people1.setToloc(map.get("TOLOC"));
            people1.setFromloc(map.get("FROMLOC"));
            people1.setLot01(map.get("LOT01"));

//            people1.setLot04(map.get("LOTTABLE04"));

//            people1.setWod001( map.get("WOD001")==null ? "" : map.get("WOD001"));
//            people1.setStorerkey(map.get("STORERKEY")==null ? "" : map.get("STORERKEY"));
//            //我的数据中这两行为空，一定要注意这里，如果出现了一个null
//            // （也就是说你的Excel数据突然有某个单元格是没有数据的）
//            // 就会报错，最好还是写一个if语句判断一下每个值，如果为null,就像我下面一样直接赋值为""
//            people1.setWod003(map.get("WOD003"));
//            people1.setWod004(map.get("WOD004"));
//            people1.setWod005(map.get("WOD005"));
//            people1.setWod009(map.get("WOD009"));
//            people1.setWod010(map.get("WOD010"));
//            people1.setWod011(map.get("WOD011"));
//            people1.setWod012(map.get("WOD012"));
//            people1.setWod013("".equals(map.get("WOD013")) ? 0 : Double.parseDouble(map.get("WOD013")));
//            people1.setWod014("".equals(map.get("WOD014")) ? 0 : Double.parseDouble(map.get("WOD014")));
//            people1.setWod015("".equals(map.get("WOD015")) ? 0 : Double.parseDouble(map.get("WOD015")));
//            people1.setWod016(map.get("WOD016"));
//            people1.setWod017("".equals(map.get("WOD017")) ? 0 : Double.parseDouble(map.get("WOD017")));
//            people1.setWod018("".equals(map.get("WOD018")) ? 0 : Double.parseDouble(map.get("WOD018")));
//            people1.setWod019("".equals(map.get("WOD019")) ? 0 : Double.parseDouble(map.get("WOD019")));
//            people1.setWod020(map.get("WOD020"));
//            people1.setWod023("".equals(map.get("WOD023")) ? 0 : Double.parseDouble(map.get("WOD023")));
//            people1.setWod024("".equals(map.get("WOD024")) ? 0 : Double.parseDouble(map.get("WOD024")));
//            people1.setWod025("".equals(map.get("WOD025")) ? 0 : Double.parseDouble(map.get("WOD025")));
//            people1.setWod026("".equals(map.get("WOD026")) ? 0 : Double.parseDouble(map.get("WOD026")));
//            people1.setWod027(map.get("WOD027"));
//            people1.setWod029(map.get("WOD029"));
//            people1.setMinQty("".equals(map.get("MINQTY")) ? 0 : Double.parseDouble(map.get("MINQTY")));
            people.add(people1);
        }
        return  people;

    }

    /**
     * 读取Excel数据
     * @param filePath
     * @return
     */
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 这里是判断数据的类型，数字还是日期，做一下转换
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell){
        Object value = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        if(cell!=null){
            //判断cell类型
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case NUMERIC:
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        //value = cell.getDateCellValue();
                    }else{

                        DecimalFormat df1 = new DecimalFormat("0");
                        //数字
                        value = String.valueOf(df1.format(cell.getNumericCellValue()));
                    }
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
                    break;
            }
        }else{
            value = "";
        }
        return value;
    }

}

